package com.qjdchina.sku.util;

import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ProductToExcel {
	
	public static final Logger LOGGER = LoggerFactory.getLogger(ProductToExcel.class);


	private List<String> fieldName = null; // excel数据的抬头栏，即名称栏
	private HSSFWorkbook workBook = null;// 一个excel文件
	private Map<String, List<String>> skuAttribute = null;

	public ProductToExcel(List<String> fieldName, Map<String, List<String>> skuAttribute) {
		this.fieldName = fieldName;
		this.skuAttribute = skuAttribute;
	}

	public HSSFWorkbook createWorkbook() {
		workBook = new HSSFWorkbook();// 创建一个工作簿

		HSSFSheet sheet = workBook.createSheet("Page " + 1);
		HSSFRow headRow = sheet.createRow(0); // 创建第一栏，抬头栏
		Integer index = 0;
		for (short j = 0; j < fieldName.size(); j++) {
			HSSFCell cell = headRow.createCell(j);// 创建抬头栏单元格
			// 设置单元格格式
			sheet.setColumnWidth(j, 6000);
			HSSFCellStyle style = workBook.createCellStyle();
			HSSFFont font = workBook.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			short color = HSSFColor.RED.index;
			font.setColor(color);
			style.setFont(font);
			// 将数据填入单元格
			if (fieldName.get(j) != null) {
				cell.setCellStyle(style);
				cell.setCellValue((String) fieldName.get(j));
			} else {
				cell.setCellStyle(style);
				cell.setCellValue("-");
			}

			List<String> skuAttributes = skuAttribute.get(fieldName.get(j));

			if (skuAttributes != null && skuAttributes.size() > 0) {
				String hiddenStr = "hidden" + index;
				HSSFSheet hidden = workBook.createSheet(hiddenStr);
				try {
					int size = skuAttributes.size();

					String[] arr = (String[]) skuAttributes.toArray(new String[size]);

					for (int i = 0, length = arr.length; i < length; i++) {
						String name = arr[i];
						HSSFRow row = hidden.createRow(i);
						HSSFCell hssfCell = row.createCell(0);
						hssfCell.setCellValue(name);
					}
					Name namedCell = workBook.createName();
					namedCell.setNameName(hiddenStr);
					namedCell.setRefersToFormula(hiddenStr+"!$A$1:$A$" + arr.length);
					DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenStr);
					CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, j, j);
					HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
					workBook.setSheetHidden(1, true);
					sheet.addValidationData(validation);

					index++;

				} catch (Exception e) {
					LOGGER.error("productToExcel Exception {}",e);
				}
			}
		}

		return workBook;
	}

	// 将信息写入输出流的方法。
	public void exportExcel(OutputStream os) throws Exception {
		workBook = createWorkbook();
		 workBook.write(os);
		os.close();
	}

}